Customer Case Study · Government & Public Sector Advisory
For a central government ministry study, the advisory firm had thousands of respondent signatures on creased, noisy A4 scans. HMC built an Excel VBA tool that captures each one, saves it under the right respondent's name and lets the team undo a bad capture, cutting turnaround from 200 hours to 8.
Figures indicative; validated metrics available on request.
The project for the central government ministry needed every respondent's contact validated. The evidence was a signature, captured in the field on A4 sheets, one row per respondent.
Respondents signed printed sheets in the field. Each sheet held dozens of rows from different employers and states.
Image quality varied. Folds and creases cut across rows, and scans were uneven in light and angle.
Validation needed each signature as its own file, named exactly after the respondent, so it could be matched to their contact.
Crop, paste, rename, save, check, repeat. Done by hand, the full set would take about 200 hours, with misnamed files along the way.
Automatic signature detection was unreliable on creased scans, so a person still frames each signature. The macro does the rest: naming, saving, highlighting and moving on.
Select a step to see what happened.
We timed analysts cropping signatures by hand.
Deliverable: Time-and-error baseline.
Split the task: judgement for a person, routine for the macro.
Deliverable: Flow chart and UserForm layout.
A small UserForm with two buttons and a path box.
Deliverable: Macro-enabled workbook and form.
Analysts were productive the same day.
Deliverable: One-page guide.
Batches were split by state, such as AP & Telangana, and run in parallel.
Deliverable: Named signature library for validation.
First the original screens, then a working replica of the tool on a synthetic sheet. Try it: capture a signature, save it, make a mistake and RESET.
Click any screen to enlarge it. Respondent names, phone numbers, addresses and signatures are pixelated.
Click a signature on the scan to capture it (the dashed box is your screenshot). Press SAVE to file it under the highlighted respondent's name. Capture the wrong row, or clear the path, to see the checks and RESET at work.
Synthetic sheet: respondent names, employers and signatures are generated. The clean-up stages illustrate ink isolation, despeckling and trimming.
Every file you saved above lands here, matched to the respondent's contact record. Run validation to see how a clean, correctly named library flows straight into contact checks, and how wrong or partial captures get caught.
Four analysts, one tool each. Press Run to watch every state's batch fill, with the tool's clock running against what the same work would have cost by hand.
Tool: about 5 seconds per signature. By hand: about 2 minutes. State split is illustrative.
The person still frames each signature, but everything else (naming, saving, tracking and correcting) now takes seconds.
Each square is one hour of analyst time
Seconds per signature, by step
Figures indicative; validated metrics available on request.
When scans are too messy for full automation, keep the person on the hard bit and automate every step around it.
Taking names from the respondent list removed the most common error: the right image under the wrong name.
RESET made mistakes cheap to fix in the moment, instead of costly to find during validation.